import sys
from pathlib import Path

file = Path(__file__).resolve()
parent, top = file.parent, file.parents[2]

sys.path.append(str(top))
try:
    sys.path.remove(str(parent))
except ValueError: # Already removed
    pass

__package__ = 'src.model'

from ..utils.ids import create_id, datetime_8, get_ip_address
from sqlalchemy.dialects.mysql import LONGTEXT
from sqlalchemy import Column, String, BigInteger, CHAR, DateTime, Integer
from ..utils.database import Base, web_engine, WebSessionLocal
import uuid


class Failure(Base):
    __tablename__ = 'tb_failure'
    __table_args__ = {'comment': '缺陷表', 'mysql_charset': 'utf8mb4', 'extend_existing': True}

    id = Column(BigInteger, primary_key=True, autoincrement=True, comment='id')
    failure_type = Column(String(36), nullable=False, comment='缺陷类型')
    failure_name = Column(String(36), nullable=False, comment='缺陷名称')
    failure_id = Column(BigInteger, nullable=False, comment='缺陷 id 做关联查询')
    del_flag = Column(
        CHAR(1), nullable=False, server_default='0', comment="是否删除, 1-已删除, 0-未删除")


# 每个 panel 
class Panel(Base):
    __tablename__ = 'tb_web_panel'
    __table_args__ = {'comment': 'panel 表', 'mysql_charset': 'utf8mb4', 'extend_existing': True}

    panel_code = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    server_host = Column(String(16), nullable=False, default=get_ip_address, comment='ai 服务器所在的 ip 地址')
    lot_name = Column(String(36), nullable=False, comment='lot名称')
    material_name = Column(String(36), nullable=False, comment='料号名')
    bar_code = Column(String(64), nullable=False, comment='panel 对应的识别码')
    ai = Column(CHAR(1), nullable=False, server_default='0', comment='AI 检测结果, 0-OK, 1-NG')
    aoi = Column(CHAR(1), nullable=False, server_default='0', comment='AOI 检测结果, 0-OK, 1-NG')
    judgment = Column(CHAR(1), nullable=False, server_default='0', comment='人工复判结果, 0-OK, 1-NG')
    start_time = Column(DateTime, default=datetime_8, comment='aoi 开始时间')
    img_roi = Column(String(255), comment='图片的有效区域')


class Workstation(Base):
    __tablename__ = 'tb_workstation'
    __table_args__ = {'comment': '工站表', 'mysql_charset': 'utf8mb4', 'extend_existing': True}

    id = Column(BigInteger, primary_key=True, autoincrement=True, comment='id')
    workstation_name = Column(String(36), nullable=False, comment='工站名')
    del_flag = Column(
        CHAR(1), nullable=False, server_default='0', comment="是否删除, 1-已删除, 0-未删除")


# 每个 panel 对应的图片
class PanelImage(Base):
    __tablename__ = 'tb_web_panel_image'
    __table_args__ = {'comment': 'panel 图片表', 'mysql_charset': 'utf8mb4', 'extend_existing': True}

    panel_image_code = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    panel_code = Column(String(36), nullable=False, comment='panel code 做关联')
    image_path = Column(String(255), comment='图片路径')
    image_name = Column(String(36), nullable=False, comment='图片名')
    thumb_file_path = Column(String(255), comment='图片的缩略图路径')
    detection_info = Column(LONGTEXT, comment='检测信息')
    aoi_failures = Column(LONGTEXT, default="", comment='过滤的 aoi 缺陷信息')
    pattern_failure = Column(String(32), comment='pattern 缺陷')


class PanelWorkstation(Base):
    __tablename__ = 'tb_panel_workstation'
    __table_args__ = {'comment': 'panel workstation 表', 'mysql_charset': 'utf8mb4', 'extend_existing': True}

    panel_workstation_code = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    panel_code = Column(String(36), nullable=False, comment='panel code 做关联')
    workstation_code = Column(String(36), nullable=False, comment='工站 code 做关联')
    detection_info = Column(LONGTEXT, comment='检测坐标信息')
    image_path = Column(String(255), comment='图片路径')
    aoi = Column(CHAR(1), nullable=False, server_default='0', comment="AOI 检测结果 1-NG, 0-OK")
    ai = Column(CHAR(1), nullable=False, server_default='0', comment="AI 检测结果 1-NG, 0-OK")
    judgment = Column(CHAR(1), nullable=False, server_default='0', comment="人工复判结果 1-NG, 0-OK")


class User(Base):
    __tablename__ = 'tb_user'
    __table_args__ = {'comment': '用户表', 'mysql_charset': 'utf8mb4', 'extend_existing': True}
    id = Column(BigInteger, primary_key=True, autoincrement=True, comment='id')
    user_code = Column(String(36), nullable=False, default=create_id, comment='user code')
    user_name = Column(String(36), nullable=False, comment='user 名')
    passwd = Column(LONGTEXT, nullable=False, comment='user 名')
    role_code = Column(String(36), nullable=False, comment='用户角色 code, 用于关联 role 表')


class Role(Base):
    __tablename__ = 'tb_role'
    __table_args__ = {'comment': '角色表', 'mysql_charset': 'utf8mb4', 'extend_existing': True}
    id = Column(BigInteger, primary_key=True, autoincrement=True, comment='id')
    role_code = Column(String(36), nullable=False, default=create_id, comment='角色 code')
    role_name = Column(String(36), nullable=False, comment='角色名')


# 每个远程拷贝任务的时间点对应的图片
class ScpJob(Base):
    __tablename__ = 'tb_scp_job'
    __table_args__ = {'comment': '远程拷贝任务表', 'mysql_charset': 'utf8mb4', 'extend_existing': True}

    job_code = Column(String(36), primary_key=True, default=lambda: str(uuid.uuid4()))
    status = Column(CHAR(1), server_default='1', comment='拷贝任务状态, 0-无数据, 1-复制排队中, 2-复制中, 3-已停止, 4-复制已完成, 5-推理排队中, 6-推理中, 7-出错, 8-推理已完成')
    aoi_hostname = Column(String(36), nullable=False, comment='远程服务器 aoi')
    start_datetime = Column(DateTime, default=datetime_8, comment='拷贝任务开始时间')
    end_datetime = Column(DateTime, default=datetime_8, comment='拷贝任务结束时间')
    last_flag = Column(CHAR(1), server_default='0', comment="是否是最后一个拷贝任务, 1-是, 0-不是")


if __name__ == "__main__":

    flag = "done"
    new_flag = None

    # 创建表
    Base.metadata.create_all(bind=web_engine)

    # table_name = ["tb_failure", "tb_failure_image", "tb_scp_job", 'tb_panel_image', 'tb_material', 'tb_aoi']
    # attr_name = "server_host"
    # column_type = "varchar(16)"
    # comment = "ai 服务器所在的 ip 地址"

    # # # 新建料号表
    # # # 先删除表
    # # with WebSessionLocal() as session:
    # #     session.execute(text('DROP TABLE %s ' % (table_name)))

    # # 修改表结构
    with WebSessionLocal() as session:
        photo_dir = "/ssd1T/data/jd/NG_A2C0375270200241223351329N04AX_1225152752_A1/photo"
        tname = "tb_panel_image"
        attr_name = "comment"
        column_type = "varchar(50)"
        comment = "lot 备注"

        panel_code = "20241231-160104-7e28"

        workstation_names = ["偏前外观", "偏前电观", "偏后电观", "偏后外观", "Bonding", "HK", "BS"]

        workstation_codes = ["20250107-112659-e829", "20250107-112659-1f67", "20250107-112659-13e3", "20250107-112659-be3c", "20250107-112659-a496", "20250107-112659-e3d4", "20250107-112659-3739"]

        # for workstation_name in workstation_names:
        #     workstation = Workstation(workstation_name=workstation_name)
        #     session.add(workstation)
        
        # session.commit()

        # 添加记录
        # for filename in os.listdir(photo_dir):c
        #     file_path = os.path.join(photo_dir, filename)

        #     # panel_image = PanelImage(panel_code=panel_code, file_path="/ssd1T/data/jd/photo/Second3_White255_box.bmp")
        #     panel_image = PanelImage(panel_code=panel_code, file_path=file_path)
        #     session.add(panel_image)
        #     session.commit()

        # for workstation_code in workstation_codes:
        #     panel_workstation = PanelWorkstation(panel_code=panel_code, workstation_code=workstation_code)
        #     session.add(panel_workstation)
        #     session.commit()

        # root_folder = "/ssd1T/temp10"
        # for foldername in os.listdir(root_folder):
        #     bar_code = foldername.split("_")[1]
        #     lot_code = "20241231-160100-522f"
        #     panel = Panel(lot_code=lot_code, bar_code=bar_code)
        #     session.add(panel)

        #     session.commit()

        #     panel_code = panel.panel_code

        #     for workstation_code in workstation_codes:
        #         panel_workstation = PanelWorkstation(panel_code=panel_code, workstation_code=workstation_code)
        #         session.add(panel_workstation)
        #         session.commit()
        #     panel_folder = os.path.join(root_folder, foldername)

        #     for panelname in os.listdir(panel_folder):
        #         if panelname.lower().endswith('.bmp'):
        #             panel_path = os.path.join(panel_folder, panelname)
        #             panel_iamge = PanelImage(panel_code=panel_code, file_path=panel_path)

        #             json_file = os.path.join(panel_folder, panelname.replace("bmp", "json"))

        #             if os.path.exists(json_file):
        #                 # {"Z221": [2000, 5000, 3000, 6000]}
        #                 detection_info = {}
        #                 with open(json_file) as f:
        #                     json_data = json.load(f)
        #                     shapes = json_data.get("shapes")
        #                     for shape in shapes:
        #                         failure_type = shape["label"]
        #                         box = shape["points"]
        #                         if box[0][0] > box[1][0]:
        #                             failure_box = [box[1][0], box[1][1], box[0][0], box[0][1]]
        #                         else:
        #                             failure_box = [box[0][0], box[0][1], box[1][0], box[1][1]]

        #                         if not detection_info.get(failure_type):
        #                             detection_info[failure_type] = []
        #                         detection_info[failure_type].append(failure_box)
        #                 panel_iamge.detection_info = json.dumps(detection_info)
        #             session.add(panel_iamge)

        #     session.commit()

        # judgment = Column(Integer, default=0, comment='人工复判结果, 0-未复判, 1-OK, 2-NG')
        # table_name = "tb_panel"
        # attr_name = "judgment"
        # column_type = "char(1)"
        # default = '0'
        # comment = "人工复判结果, 0-未复判, 1-OK, 2-NG"

        # # 添加列
        # session.execute(text('ALTER TABLE %s ADD COLUMN %s %s NOT NULL DEFAULT "%s" COMMENT "%s"' % (table_name, attr_name, column_type, default, comment)))
    #     # tname = "tb_panel_image"
    #     # attr_name = "start_time"
    #     # column_type = "datetime"
    #     # comment = "开始时间"
    #     # session.execute(text('ALTER TABLE %s ADD COLUMN %s %s NOT NULL COMMENT "%s"' % (tname, attr_name, column_type, comment)))

    #     # tname = "tb_panel_image"
    #     # attr_name = "end_time"
    #     # column_type = "datetime"
    #     # comment = "结束时间"
    #     # session.execute(text('ALTER TABLE %s ADD COLUMN %s %s NOT NULL COMMENT "%s"' % (tname, attr_name, column_type, comment)))


    #     tname = "tb_scp_job"
    #     attr_name = "del_flag"
    #     column_type = "char(1)"
    #     comment = "逻辑删除, 1-删除, 0-不删除"
    #     session.execute(text('ALTER TABLE %s ADD COLUMN %s %s NOT NULL COMMENT "%s"' % (tname, attr_name, column_type, comment)))

    #     session.commit()
      
        # session.execute(text('ALTER TABLE %s MODIFY %s %s NOT NULL DEFAULT "%s" COMMENT "%s"' % (table_name, attr_name, column_type, default, comment)))

    #     if not flag:

    #         # 添数据到 tb_material
    #         material_name = 'G163A'
    #         background_image = "/data/jd/material/G163A/PowerOff.bmp"
        
    #         material = Material(material_name=material_name, background_image=background_image, parameter=json.dumps(all_parameter))
    #         session.add(material)
    #         session.commit()

    #     if not flag:

    #         # 更新 tb_failure 表
    #         table_name = "tb_failure"

    #         # 逻辑删除
    #         del_failure = ["OK", "P304"]
    #         new_failure = ["P4FAKE", "P5FAKE"]

    #         for failure in del_failure:
    #             failure_obj = session.query(Failure).filter_by(failure_name=failure).first()
    #             failure_obj.del_flag = '1'

    #         current_failure_id = session.query(Failure).order_by(Failure.id.desc()).limit(1).first().failure_id
    #         for failure in new_failure:
    #             current_failure_id += 1
    #             failure_obj = Failure(failure_type=failure, failure_name=failure, failure_id=current_failure_id)
    #             session.add(failure_obj)

    #         session.commit()

    #     if not new_flag:
    #         table_name = "tb_panel_image"
    #         attr_name = "ai_result"
    #         column_type = "int"
    #         comment = "AI 检测结果, 1-OK, 2-NG, 3-Gray"
    #         session.execute(text('ALTER TABLE %s MODIFY %s %s COMMENT "%s"' % (table_name, attr_name, column_type, comment)))

    #         session.commit()

    # # 添加新的 failure id
    # table_name = "tb_failure"

    # # 逻辑删除
    # failure = ["PH01", "大板元不良"]

    # with WebSessionLocal() as session:
    #     failure_obj = Failure(id=15, failure_type=failure[1], failure_name=failure[0], failure_id=14)
    #     session.add(failure_obj)

        # session.commit()
    # with WebSessionLocal() as session:
    #     aoi1 = AOI(aoi_name="aoi1", hostname="192.168.2.13", username="HUAWEI", password="123456", lotdir="D:\jdipic", logdir="D:\jdilog")
    #     session.add(aoi1)

    #     aoi2 = AOI(aoi_name="aoi2", hostname="192.168.3.2", username="Administrator", password="123456", lotdir="D:\jdipic", logdir="D:\jdilog")
    #     session.add(aoi2)

    #     session.commit()
